package net.sky.oaxz.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import net.sky.oaxz.bean.XuzhaoLianhe;
import net.sky.oaxz.bean.XuzhaoModel;
import net.sky.oaxz.bean.XuzhaoNew;
import net.sky.oaxz.bean.XuzhaoStuSch;
import net.sky.oaxz.bean.XuzhaoSum;
import net.sky.oaxz.bean.XuzhaoTemp;
import net.sky.oaxz.bean.XzNewstudent;

@SuppressWarnings("unchecked")
public class XuzhaoNewDao extends BaseDao{
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	
	/**
	 * add xuzhaonew data 
	 */
	public int addxz(XuzhaoNew xuzhao){
		StringBuffer sql=new StringBuffer("insert into xuzhaonew(xz_id,xz_sch_id,xz_gra_id,xz_sub_id,xz_tea_id,xz_basenum,xz_winbase,xz_spring,xz_addtime,xz_operatetime) " +
				"values(SQ_XUZHAONEW_ID.nextval,?,?,?,?,?,?,?,sysdate-1,sysdate)");
		String [] params=new String[] {xuzhao.getXzSchId()+"",xuzhao.getXzGraId()+"",xuzhao.getXzSubId()+"",
				xuzhao.getXzTeaId()+"",xuzhao.getXzBasenum()+"",xuzhao.getXzWinbase()+"",xuzhao.getXzSpring()+""};
		return this.executeSQL(sql.toString(), params);
	}
	/**
	 * add xuzhaoNewStudent data
	 */
	public int addxzNewstu(XzNewstudent stu){
		StringBuffer sql=new StringBuffer("insert into xz_newstudent values(SQ_XZNEWSTUDENT_ID.NEXTVAL,?,?)");
		String [] params=new String[] {stu.getStuSchId()+"",stu.getStuNum()+""};
		return this.executeSQL(sql.toString(), params);
	}
	/**
	 * 按ID查询一条信息
	 * @param xzId
	 * @return
	 */
	public XuzhaoLianhe searchById(int xzId){
		XuzhaoLianhe xln=null;
		StringBuffer sql=new StringBuffer("select x.*,xsch.sch_name sn,xg.gra_name gn,xsub.sub_name su,xt.tea_name tn " +
				"from xuzhaonew x ,xz_school xsch, xz_grade xg, xz_subject xsub, xz_teacher xt " +
				"where x.xz_sch_id=xsch.sch_id and x.xz_gra_id=xg.gra_id and x.xz_sub_id=xsub.sub_id and " +
				"x.xz_tea_id=xt.tea_id and x.xz_id='"+xzId+"'");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				xln=new XuzhaoLianhe();
				xln.setSchName(rs.getString("sn"));
				xln.setGraName(rs.getString("gn"));
				xln.setSubName(rs.getString("su"));
				xln.setTeaName(rs.getString("tn"));
				xln.setXzSchId(rs.getInt("xz_sch_id"));
				xln.setXzSubId(rs.getInt("xz_sub_id"));
				xln.setXzGraId(rs.getInt("xz_gra_id"));
				xln.setXzTeaId(rs.getInt("xz_tea_id"));
				xln.setXzBasenum(rs.getInt("xz_basenum"));
				xln.setXzWinter(rs.getInt("xz_winter"));
				xln.setXzSpring(rs.getInt("xz_spring"));
				xln.setXzWinbase(rs.getInt("xz_winbase"));
				xln.setXzId(rs.getInt("xz_id"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return xln;
	}
	
	/**
	 * 按续招ID查询
	 * @param xzId
	 * @return
	 */
	public XuzhaoNew searchById1(int xzId){
		XuzhaoNew xzo=null;
		StringBuffer sql=new StringBuffer("select * from xuzhaonew x where x.xz_id='"+xzId+"'");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				xzo=new XuzhaoNew();
				xzo.setXzSchId(rs.getInt("xz_sch_id"));
				xzo.setXzSubId(rs.getInt("xz_sub_id"));
				xzo.setXzGraId(rs.getInt("xz_gra_id"));
				xzo.setXzTeaId(rs.getInt("xz_tea_id"));
				xzo.setXzBasenum(rs.getInt("xz_basenum"));
				xzo.setXzWinter(rs.getInt("xz_winter"));
				xzo.setXzSpring(rs.getInt("xz_spring"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return xzo;
	}
	/**
	 * show English oneGrade
	 */
	public List searchXuZhao(){
		List list =new ArrayList();
		StringBuffer sql=new StringBuffer("select s.sub_name sn,g.gra_name gn,sum(xz_basenum) bas,sum(xz_winter) win,sum(xz_spring) spr " +
				"from xuzhaonew xz " +
				"join xz_subject s on xz.xz_sub_id=s.sub_id " +
				"join xz_grade g on xz.xz_gra_id = g.gra_id " +
				"group by grd_level,xz_gra_id,xz_sub_id,g.gra_name,s.sub_name " +
				"order by grd_level,xz_sub_id,xz_gra_id");
//		trunc(xz.xz_operatetime) =trunc((select max(xz_operatetime) from xuzhaonew))
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				XuzhaoTemp xt=new XuzhaoTemp();
				xt.setSubName(rs.getString("sn"));
				xt.setGraName(rs.getString("gn"));
				xt.setSumBasenum(rs.getInt("bas"));
				xt.setSumWinter(rs.getInt("win"));
				xt.setSumSpring(rs.getInt("spr"));
				list.add(xt);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	/**
	 * 按教师统计
	 * @return
	 */
	public List searchTeacherSum(){
		List list =new ArrayList();
		StringBuffer sql=new StringBuffer("select t.tea_name tchr,xz.xz_gra gr,sum(xz_basenum) bas,sum(xz_winbase) wb,sum(xz_winter) wt,sum(xz_spring) spr " +
				"from xuzhaonew xz join xz_teacher t on xz.xz_tea_id=t.tea_id " +
//				"where xz.xz_sub_id=2 " +
				"group by t.tea_name,xz.xz_gra " +
				"having sum(xz_basenum+xz_winbase) >0 " +
				"order by sum(xz_spring)/sum(xz_basenum+xz_winbase) desc,5 desc");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				XuzhaoModel xt=new XuzhaoModel();
				xt.setTeacherName(rs.getString("tchr"));
				xt.setBaseNum(rs.getInt("bas"));
				xt.setSpring(rs.getInt("spr"));
				xt.setWinBase(rs.getInt("wb"));
				xt.setGraId(rs.getInt("gr"));
				xt.setWinter(rs.getInt("wt"));
				list.add(xt);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
//	"select grd_levelName grp,s.sub_name sub,sum(xz_basenum) bas,sum(xz_winter) win,sum(xz_spring) spr ,sum(xz_winter)/sum(xz_basenum),1 ,g.grd_level " +
//	"from xuzhaonew xz " +
//	"join xz_grade g on xz.xz_gra_id=g.gra_id " +
//	"join xz_subject s on xz.xz_sub_id=s.sub_id " +
//	"group by grd_levelName,g.grd_level,s.sub_name " +
//	"union " +
//	"select grd_levelName grp,'合计',sum(xz_basenum) bas,sum(xz_winter) win,sum(xz_spring) spr ,sum(xz_winter)/sum(xz_basenum),1,g.grd_level " +
//	"from xuzhaonew xz " +
//	"join xz_grade g on xz.xz_gra_id=g.gra_id " +
//	"join xz_subject s on xz.xz_sub_id=s.sub_id " +
//	"group by grd_levelName,g.grd_level " +
//	"union " +
//	"select'','统计',sum(xz_basenum),sum(xz_winter),sum(xz_spring),sum(xz_winter)/sum(xz_basenum),2 ,2 " +
//	"from xuzhaonew xz " +
//	"order by 7,8,3"
	/**
	 * 按学科统计
	 * @return
	 */
	public List searchSubSum(){
		List list =new ArrayList();
		StringBuffer sql=new StringBuffer("select grd_levelName grp,s.sub_name sub,sum(xz_basenum) bas,sum(xz_winbase) wb,sum(xz_spring) spr ,sum(xz_spring)/sum(xz_basenum+xz_winbase),1 ,g.grd_level " +
				"from xuzhaonew xz " +
				"join xz_grade g on xz.xz_gra_id=g.gra_id " +
				"join xz_subject s on xz.xz_sub_id=s.sub_id " +
				"group by grd_levelName,g.grd_level,s.sub_name " +
				"union " +
				"select grd_levelName grp,'合计',sum(xz_basenum) bas,sum(xz_winbase) wb,sum(xz_spring) spr ,sum(xz_spring)/sum(xz_basenum+xz_winbase),1,g.grd_level " +
				"from xuzhaonew xz " +
				"join xz_grade g on xz.xz_gra_id=g.gra_id " +
				"join xz_subject s on xz.xz_sub_id=s.sub_id " +
				"group by grd_levelName,g.grd_level " +
				"union " +
				"select'','统计',sum(xz_basenum),sum(xz_winbase),sum(xz_spring),sum(xz_spring)/sum(xz_basenum+xz_winbase),2 ,2 " +
				"from xuzhaonew xz order by 7,8,3");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				XuzhaoModel xt=new XuzhaoModel();
				xt.setGroupName(rs.getString("grp"));
				xt.setSubName(rs.getString("sub"));
				xt.setBaseNum(rs.getInt("bas"));
				xt.setWinBase(rs.getInt("wb"));
				xt.setSpring(rs.getInt("spr"));
				list.add(xt);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	/**
	 * 前台按校区统计
	 * @return
	 */
	public List searchAllSum(){
		List list =new ArrayList();
		StringBuffer sql=new StringBuffer("select s.sch_seq hid,s.sch_name2 sch,sum(xz_basenum) bas,sum(xz_winbase) wb,sum(xz_spring) spr,1 ,sum(xz_spring)/(sum(xz_basenum)+sum(xz_winbase)) " +
				"from xuzhaonew xz " +
				"join xz_school s on xz.xz_sch_id=s.sch_id " +
				"group by  s.sch_seq , s.sch_name2 " +
				"union " +
				"select 0,'统 计',sum(xz_basenum) bas,sum(xz_winbase) wb,sum(xz_spring) spr,2 ,sum(xz_spring)/(sum(xz_basenum)+sum(xz_winbase)) " +
				"from xuzhaonew xz " +
				"order by 6,7 desc,5 desc");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				XuzhaoModel xt=new XuzhaoModel();
				xt.setSchoolName(rs.getString("sch"));
				xt.setBaseNum(rs.getInt("bas"));
				xt.setWinBase(rs.getInt("wb"));
				xt.setSpring(rs.getInt("spr"));
				xt.setSchId(rs.getInt("hid"));
				list.add(xt);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	/**
	 * 后台按校区统计
	 * @return
	 */
//	select s.sch_id hid,s.sch_name sch,sum(xz_basenum) bas,sum(xz_winter) win,sum(xz_spring) spr,1 ,sum(xz_winter)/sum(xz_basenum) " +
//	"from xuzhaonew xz " +
//	"join xz_school s on xz.xz_sch_id=s.sch_id " +
//	"group by  s.sch_id , s.sch_name " +
//	"union " +
//	"select 0,'统 计',sum(xz_basenum) bas,sum(xz_winter) win,sum(xz_spring) spr,2 ,sum(xz_winter)/sum(xz_basenum) " +
//	"from xuzhaonew xz " +
//	"order by 6,7 desc,5 desc
	public List searchAllSumBack(){
		List list =new ArrayList();
		StringBuffer sql=new StringBuffer("select s.sch_id hid,s.sch_name sch,sum(xz_basenum) bas,sum(xz_winbase) wb,sum(xz_spring) spr,1 ,sum(xz_spring)/sum(xz_basenum+xz_winbase) " +
				"from xuzhaonew xz " +
				"join xz_school s on xz.xz_sch_id=s.sch_id " +
				"group by  s.sch_id , s.sch_name " +
				"union " +
				"select 0,'统 计',sum(xz_basenum) bas,sum(xz_winbase) wb,sum(xz_spring) spr,2 ,sum(xz_spring)/sum(xz_basenum+xz_winbase) " +
				"from xuzhaonew xz order by 6,7 desc,5 desc");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				XuzhaoModel xt=new XuzhaoModel();
				xt.setSchoolName(rs.getString("sch"));
				xt.setBaseNum(rs.getInt("bas"));
				xt.setWinBase(rs.getInt("wb"));
				xt.setSpring(rs.getInt("spr"));
				xt.setSchId(rs.getInt("hid"));
				list.add(xt);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	
	/**
	 * show all xuzhao
	 * @return
	 */
	public List searchAllxz(){
		List list =new ArrayList();
		XuzhaoModel xm=null;
		StringBuffer sql=new StringBuffer("select x.xz_id, g.gra_name a,s.sub_name b,t.tea_name c,x.xz_basenum d,x.xz_winbase w,x.xz_winter e,x.xz_spring f " +
				"from xuzhaonew x,xz_grade g,xz_subject s,xz_teacher t " +
				"where x.xz_gra_id=g.gra_id and x.xz_sub_id=s.sub_id and x.xz_tea_id=t.tea_id order by t.tea_name");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				xm=new XuzhaoModel();
				xm.setXzId(rs.getInt("xz_id"));
				xm.setGraName(rs.getString("a"));
				xm.setSubName(rs.getString("b"));
				xm.setTeaName(rs.getString("c"));
				xm.setBaseNum(rs.getInt("d"));
				xm.setWinter(rs.getInt("e"));
				xm.setSpring(rs.getInt("f"));
				xm.setWinBase(rs.getInt("w"));
				list.add(xm);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}

	/**
	 * 按校区查询、1
	 * @return
	 */
	public List searchBySchId(int schId){
		List list =new ArrayList();
		StringBuffer sql=new StringBuffer("select sch.sch_name sn, x.xz_id,g.gra_name a, s.sub_name b, t.tea_name c, x.xz_basenum d,x.xz_winbase w, x.xz_spring f " +
				"from xuzhaonew x,xz_grade g,xz_subject s,xz_teacher t,xz_school sch " +
				"where x.xz_sch_id=sch.sch_id and x.xz_gra_id=g.gra_id and x.xz_sub_id=s.sub_id and x.xz_tea_id=t.tea_id and x.xz_sch_id="+schId+" " +
						"order by t.tea_name");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				 XuzhaoModel xz3=new XuzhaoModel();
				 xz3.setXzId(rs.getInt("xz_id"));
				 xz3.setGraName(rs.getString("a"));
				 xz3.setSubName(rs.getString("b"));
				 xz3.setTeaName(rs.getString("c"));
				 xz3.setBaseNum(rs.getInt("d"));
				 xz3.setSpring(rs.getInt("f"));
				 xz3.setSchoolName(rs.getString("sn"));
				 xz3.setWinBase(rs.getInt("w"));
				 list.add(xz3);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	
	/**
	 * 按校区查询、2
	 * @return
	 */
	public XuzhaoSum searchBySchIdSum(int schId){
		XuzhaoSum xzSum=null;
		StringBuffer sql=new StringBuffer("select sum(xz_basenum) bas,sum(xz_winbase) wb,sum(xz_spring) spr " +
				"from xuzhaonew xz join xz_subject s on xz.xz_sub_id = s.sub_id " +
				"join xz_grade g on xz.xz_gra_id = g.gra_id " +
				"where xz.xz_sch_id="+schId);
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				xzSum=new XuzhaoSum();
				xzSum.setBasSum(rs.getInt("bas"));
				xzSum.setWinSum(rs.getInt("wb"));
				xzSum.setSprSum(rs.getInt("spr"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return xzSum;
	}

	/**
	 * 按校区查询新生，后台用
	 * @return
	 */
	public XzNewstudent searchBySchIdStu(int schId){
		XzNewstudent xzNewStu=null;
		StringBuffer sql=new StringBuffer("select sum(n.stu_num) win ,sum(n.stu_sprnum) spr,sum(n.stu_fangzhen) fz from xz_newstudent n where n.stu_sch_id="+schId);
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				xzNewStu=new XzNewstudent();
				xzNewStu.setStuNum(rs.getInt("win"));
				xzNewStu.setStusprNum(rs.getInt("spr"));
				xzNewStu.setStuFangzhen(rs.getInt("fz"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return xzNewStu;
	}
	/**
	 * 按校区查询新生,前台用
	 * @return
	 */
	public XzNewstudent searchBySchIdStu1(int schId){
		XzNewstudent xzNewStu=null;
		StringBuffer sql=new StringBuffer("select sum(n.stu_num) win ,sum(n.stu_sprnum) spr,sum(n.stu_fangzhen) fz from xz_newstudent n where n.stu_seq="+schId);
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				xzNewStu=new XzNewstudent();
				xzNewStu.setStuNum(rs.getInt("win"));
				xzNewStu.setStusprNum(rs.getInt("spr"));
				xzNewStu.setStuFangzhen(rs.getInt("fz"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return xzNewStu;
	}
	/**
	 * 后台按校区查询新生
	 * @return
	 */
	public XzNewstudent searchBySchIdBack(int schId){
		XzNewstudent xzNewStu=null;
		StringBuffer sql=new StringBuffer("select * from xz_newstudent where stu_sch_id="+schId);
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				xzNewStu=new XzNewstudent();
				xzNewStu.setStuNum(rs.getInt("stu_num"));
				xzNewStu.setStusprNum(rs.getInt("stu_sprnum"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return xzNewStu;
	}
	/**
	 * 按校区查询新生显示后修改
	 * @return
	 */
	public XuzhaoStuSch searchBySchIdStuUp(int schId){
		XuzhaoStuSch xzStu=null;
		StringBuffer sql=new StringBuffer("select n.*,s.sch_name sn from xz_newstudent n ,xz_school s where n.stu_sch_id=s.sch_id and n.stu_sch_id="+schId);
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				xzStu=new XuzhaoStuSch();
				xzStu.setStuId(rs.getInt("stu_id"));
				xzStu.setStuSchId(rs.getInt("stu_sch_id"));
				xzStu.setStuNum(rs.getInt("stu_num"));
				xzStu.setSchName(rs.getString("sn"));
				xzStu.setSprNum(rs.getInt("stu_sprnum"));
				xzStu.setStuFangzhen(rs.getInt("stu_fangzhen"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return xzStu;
	}
	/**
	 * update NewStudent
	 * @param lx
	 * @return
	 */
     public int updateStu(XzNewstudent stu){
    	StringBuffer sql =new StringBuffer("update xz_newstudent n set n.stu_sprnum=?,n.stu_fangzhen=? where n.stu_id=?");
		String [] params=new String[]{stu.getStusprNum()+"",stu.getStuFangzhen()+"",stu.getStuId()+""};
		return this.executeSQL(sql.toString(), params);
	}

	/**
	 * update data
	 * @param lx
	 * @return
	 */
     public int updatexz(XuzhaoNew xz){
    	 StringBuffer sql =new StringBuffer("update xuzhaonew x set x.xz_basenum=?,x.xz_winbase=?,x.xz_spring=? where x.xz_id=?");
		String [] params=new String[]{xz.getXzBasenum()+"",xz.getXzWinbase()+"",xz.getXzSpring()+"",xz.getXzId()+""};
		return this.executeSQL(sql.toString(), params);
	}
     /**
 	 * delete data
 	 * @param xzid
 	 * @return
 	 */
 	public int deletexz(int xzid){
 		StringBuffer sql=new StringBuffer("delete from xuzhaonew x where x.xz_id= ?");
 		String [] params = new String [] {xzid+""};
 		int count = this.executeSQL(sql.toString(), params);
 		return count;
 	}
}
